﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace bmapi.SQLServerAccess
{
    public class SQLHelp
    {
        public static readonly string connstr = ConfigurationManager.ConnectionStrings["connectStr"].ToString();
        #region 非查询方法的封装 
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameter)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (parameter != null)
                {
                    cmd.Parameters.AddRange(parameter);
                }
                int result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return result;
            }
        }
        #endregion
        #region 查询一个值的封装
        public static string ExecuteScalar(string sql, params SqlParameter[] parameter)
        {
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (parameter != null)
                {
                    cmd.Parameters.AddRange(parameter);
                }
                Object obj = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if (obj != null)
                {
                    return obj.ToString();
                }
                else
                {
                    return null;
                }

            }
        }
        #endregion
        #region 查询多个值的封装
        public static DataTable ExecuteScalars(string sql, params SqlParameter[] paramter)
        {
            DataSet set = new DataSet();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (paramter != null)
                {
                    cmd.Parameters.AddRange(paramter);
                }
                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(set);
                }
                cmd.Parameters.Clear();
                return set.Tables[0];
            }
        }
        #endregion

        /// <summary>  
        /// 执行事务，返回0或者1  
        /// </summary>  
        /// <param name="Sqlstr">sql语句</param>  
        /// <returns></returns>  
        public static int ExecTran(List<string> Sqlstr, List<SqlParameter[]> paras)
        {
            int res = 0;
            SqlConnection conn = new SqlConnection(connstr);
            conn.Open();
            // 先实例SqlTransaction类，使用这个事务使用的是con 这个连接，使用BeginTransaction这个方法来开始执行这个事务
            SqlTransaction tran = conn.BeginTransaction();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.Transaction = tran;
            try
            {
                int count = Sqlstr.Count;
                for (int i = 0; i < count; i++)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = Sqlstr[i];
                    cmd.Parameters.AddRange(paras[i]);
                    res = cmd.ExecuteNonQuery();
                }
                tran.Commit();
                return 1;
            }
            catch (Exception e)
            {
                string str = e.Message;
                tran.Rollback();
                return 0;
            }
            finally
            {
                conn.Close();
                tran.Dispose();
            }
        }

        /// <summary>
        ///  执行SQL语句,分页查询
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <param name="order">排序SQL,如"ORDER BY ID DESC"</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="pageIndex">页索引</param>
        /// <returns></returns>
        public static DataTable ExecutePageDataTable(string order, int limit, int page, string zhutablename, string query, string futablename = "", string zzidaun = "", string fuzidaun = "")
        {
            string sql = "";
            if (!string.IsNullOrEmpty(futablename))
            {
                sql = string.Format(@"SELECT {1}.*,{2}.name as Pname,COUNT(1) OVER() AS total
                                                FROM {1} ,{2}
                                                {5} and {1}.{3}={2}.{4}
                                                {0}
                                                OFFSET @beginnum ROW FETCH NEXT @endnum ROWS ONLY", order, zhutablename, futablename, zzidaun, fuzidaun, query);
            }
            else
            {
                sql = string.Format(@"SELECT *,COUNT(1) OVER() AS total
                                                FROM {1}
                                                {2}
                                                {0}
                                                OFFSET @beginnum ROW FETCH NEXT @endnum ROWS ONLY", order, zhutablename, query);
            }
            SqlParameter[] paras = new SqlParameter[]
            {
                new SqlParameter("@beginnum", SqlDbType.Int){ Value = (page - 1) * limit + 1 },
                new SqlParameter("@endnum", SqlDbType.Int){ Value = page * limit },

            };
            return ExecuteScalars(sql, paras);
        }


        //#region 获取分页的数据
        ///// <summary>
        ///// 获取分页的数据
        ///// </summary>
        ///// <param name="TblName">数据表名</param>
        ///// <param name="Fields">要读取的字段</param>
        ///// <param name="OrderField">排序字段 也可后接升降序 例：Time desc</param>
        ///// <param name="SqlWhere">查询条件</param>
        ///// <param name="PageSize">每页显示多少条数据</param>
        ///// <param name="pageIndex">当前页码</param>
        ///// <param name="TotalPage">返回值，共有多少页</param>
        ///// <param name="TotalRecord">返回值，总有多少条记录</param>
        ///// <returns></returns>
        //public static DataTable GetDataListByPage(string TblName, string Fields, string OrderField, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord)
        //{

        //    SqlConnection conn = new SqlConnection(connstr);
        //    SqlCommand comm = new SqlCommand("proc_GetListByPage", conn);

        //    comm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar, 100)).Value = TblName;
        //    comm.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar, 1000)).Value = Fields;
        //    comm.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar, 1000)).Value = OrderField;
        //    //comm.Parameters.Add(new SqlParameter("@OrderType", SqlDbType.NVarChar, 1000)).Value = OrderType;
        //    comm.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.NVarChar, 1000)).Value = SqlWhere;
        //    comm.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = PageSize;
        //    comm.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int)).Value = pageIndex;
        //    comm.Parameters.Add(new SqlParameter("@TotalPage", SqlDbType.Int));

        //    comm.Parameters["@TotalPage"].Direction = ParameterDirection.Output;//获得out出来的参数值

        //    comm.Parameters.Add(new SqlParameter("@totalRecord", SqlDbType.Int));
        //    comm.Parameters["@totalRecord"].Direction = ParameterDirection.Output;

        //    comm.CommandType = CommandType.StoredProcedure;

        //    SqlDataAdapter dataAdapter = new SqlDataAdapter(comm);
        //    DataSet ds = new DataSet();
        //    dataAdapter.Fill(ds);

        //    TotalPage = (int)comm.Parameters["@TotalPage"].Value;
        //    TotalRecord = (int)comm.Parameters["@totalRecord"].Value;

        //    comm.Parameters.Clear();
        //    conn.Close();
        //    conn.Dispose();
        //    comm.Dispose();

        //    return ds.Tables[0];
        //}
        //#endregion

    }
}